package com.shop.dao.Order;

import com.shop.dao.UserOrderDao;
import com.shop.entity.Order;
import com.shop.entity.UserOrderList;
import com.shop.exception.DAOException;
import com.shop.transaction.TransactionManager;
import com.shop.util.ConnectionUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserOrderDaoImpl implements UserOrderDao {
    @Override
    public List<Order> getAllUserOrder(int uid) {
//        System.out.println(uid + "," + pageNum + "," + pageSize);
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        List<Order> list = new ArrayList<>();
        try {
            con = TransactionManager.getTransactionConn();
            StringBuffer sql = new StringBuffer(" SELECT o.`o_id`,o.`u_id`,o.`o_status`,o.`o_pay`,o.`o_total`,o.`o_starttime` FROM orders o WHERE u_id=?");
            pstmt = (PreparedStatement) con.prepareStatement(String.valueOf(sql));
            pstmt.setObject(1,uid);
            rs = pstmt.executeQuery();
            while (rs.next()){
               int o_id = (int) rs.getObject("o_id");
               int u_id = rs.getInt("u_id");
               int o_pay = rs.getInt("o_pay");
               int o_status = rs.getInt("o_status");
               double o_total = rs.getDouble("o_total");
               String o_starttime = rs.getString("o_starttime");
                Order order = new Order(o_id,u_id,o_total,o_pay,o_status,o_starttime,null,null);
                list.add(order);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return list;
    }

    @Override
    public List<Order> getAllUserOrderPay(int uid, int o_pay) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        List<Order> list = new ArrayList<>();
        try {
            con = TransactionManager.getTransactionConn();
            StringBuffer sql = new StringBuffer("SELECT o.`o_id`,o.`u_id`,o.`o_status`,o.`o_pay`," +
                    "o.`o_total`,o.`o_starttime` FROM orders o WHERE u_id=? AND o.`o_pay`=?;");
            pstmt = (PreparedStatement) con.prepareStatement(String.valueOf(sql));
            pstmt.setObject(1,uid);
            pstmt.setObject(2,o_pay);
            rs = pstmt.executeQuery();
            while (rs.next()){
                int o_id = (int) rs.getObject("o_id");
                int u_id = rs.getInt("u_id");
                int o_status = rs.getInt("o_status");
                double o_total = rs.getDouble("o_total");
                String o_starttime = rs.getString("o_starttime");
                Order order = new Order();
                order.setO_id(o_id);
                order.setU_id(u_id);
                order.setO_status(o_status);
                order.setO_total(o_total);
                order.setO_starttime(o_starttime);
                list.add(order);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return list;
    }

    @Override
    public List<Order> getAllUserOrderSend(int uid, int o_staus) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        List<Order> list = new ArrayList<>();
        try {
            con = TransactionManager.getTransactionConn();
            StringBuffer sql = new StringBuffer("SELECT o.`o_id`,o.`u_id`,o.`o_status`,o.`o_pay`," +
                    "o.`o_total`,o.`o_starttime` FROM orders o WHERE u_id=? AND o.`o_status`=?;");
            pstmt = (PreparedStatement) con.prepareStatement(String.valueOf(sql));
            pstmt.setObject(1,uid);
            pstmt.setObject(2,o_staus);
            rs = pstmt.executeQuery();
            while (rs.next()){
                int o_id = (int) rs.getObject("o_id");
                int u_id = rs.getInt("u_id");
                int o_status = rs.getInt("o_status");
                double o_total = rs.getDouble("o_total");
                String o_starttime = rs.getString("o_starttime");
                Order order = new Order();
                order.setO_id(o_id);
                order.setU_id(u_id);
                order.setO_status(o_status);
                order.setO_total(o_total);
                order.setO_starttime(o_starttime);
                list.add(order);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return list;
    }


    @Override
    public void userUpOrder(int u_id, int o_id, int o_status) {
        System.out.println("用户更新订单");
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs =null;
        try {
            con = TransactionManager.getTransactionConn();
            StringBuffer sql = new StringBuffer("UPDATE orders o SET o.`o_status`=? WHERE o.`o_id`=? AND o.`u_id`=?;");
            pstmt = (PreparedStatement) con.prepareStatement(String.valueOf(sql));
            pstmt.setObject(1,o_status);
            pstmt.setObject(2,o_id);
            pstmt.setObject(3,u_id);
            pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
    }

    @Override
    public List<UserOrderList> getAllOrderDetialList(int u_id, String od_status, int pageNum, int pageSize) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        List<UserOrderList> list = new ArrayList<>();
        try {
            con = TransactionManager.getTransactionConn();
            StringBuffer sql = new StringBuffer("SELECT od.`od_id`,gd.`gd_id`,i.`img_path`,three.`gtt_name`,od.`od_status` " +
                    "FROM order_detail od,goods_third_type three,good_detail gd,img_path i\n" +
                    "WHERE od.`gd_id`=gd.`gd_id`AND i.`gd_id`=gd.`gd_id`\n" +
                    "AND od.`o_id`IN(SELECT o_id FROM orders WHERE u_id=?)");
            if (od_status!=null){
                sql.append(" AND od.`od_status`=?");
            }
            sql.append(" GROUP BY gd.`gd_id` LIMIT ?,?");
//            System.out.println(sql);
            pstmt = (PreparedStatement) con.prepareStatement(String.valueOf(sql));
            pstmt.setObject(1,u_id);
            //根据订单状态查询商家订单
            if (od_status!=null){
                pstmt.setObject(2,od_status);
                pstmt.setObject(3,pageNum);
                pstmt.setObject(4,pageSize);
            }else {
                pstmt.setObject(2,pageNum);
                pstmt.setObject(3,pageSize);
            }
            rs = pstmt.executeQuery();
            while (rs.next()){
                int od_id = rs.getInt("od_id");
                int gd_id = (int) rs.getObject("gd_id");
                String img_path = rs.getString("img_path");
                String gtt_name = rs.getString("gtt_name");
                int status = rs.getInt("od_status");
                UserOrderList userOrderList = new UserOrderList(od_id,gd_id,img_path,gtt_name,status);
//                System.out.println("userOrderList" + userOrderList);
                list.add(userOrderList);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return list;
    }

    @Override
    public List<Order> getAllUserOrderLike(int uid, String collection, int pageNum, int pageSize) {
        System.out.println(collection);
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        List<Order> list = new ArrayList<>();
        try {
            con = TransactionManager.getTransactionConn();
            StringBuffer sql = new StringBuffer("SELECT*FROM(SELECT o.u_id,o_total,o.o_id,o.o_pay,o.o_status,o.o_starttime,od.od_id,gd.`gd_id`,i.`img_path`,three.`gtt_name`,gd.`gd_price`,gd.`gd_buyNum`,four.`gfour_name`,od.od_status\n" +
                    "FROM good_detail gd,img_path i, order_detail od,orders o,\n" +
                    "goods_four_type four,goods_third_type three\n" +
                    " WHERE i.`gd_id`=gd.`gd_id`\n" +
                    "AND od.gd_id=gd.gd_id\n" +
                    "AND o.o_id=od.o_id\n" +
                    "AND gd.gfour_id = four.gfour_id\n" +
                    "AND gd.gtt_id = three.gtt_id\n" +
                    "AND gd.`gd_id`IN(SELECT od.`gd_id` FROM order_detail od LEFT JOIN orders o ON od.`o_id`=o.`o_id` AND o.`u_id`=? GROUP BY od.`gd_id`)\n" +
                    "GROUP BY od.od_id) res\n" +
                    "WHERE res.`gtt_name` LIKE ? LIMIT ?,?;");
            pstmt = (PreparedStatement) con.prepareStatement(String.valueOf(sql));
            pstmt.setObject(1,uid);
            pstmt.setObject(2,"%"+collection +"%");
            pstmt.setObject(3,pageNum);
            pstmt.setObject(4,pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()){
                int o_id = (int) rs.getObject("o_id");
                int u_id = rs.getInt("u_id");
                double total = rs.getDouble("o_total");
                int o_pay = rs.getInt("o_pay");
                int o_status = rs.getInt("o_status");
                String o_starttime =rs.getString("o_starttime");
                Order order = new Order(o_id,u_id,total,o_pay,o_status,o_starttime);
                list.add(order);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return list;
    }

    @Override
    public List<UserOrderList> getOrderDetailByOid(int u_id, int o_id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        List<UserOrderList> list = new ArrayList<>();
        try {
            con = TransactionManager.getTransactionConn();
            StringBuffer sql = new StringBuffer("SELECT*FROM(SELECT o.o_id,od.od_id,gd.`gd_id`,i.`img_path`,three.`gtt_name`,gd.`gd_price`,gd.`gd_buyNum`,four.`gfour_name`,od.od_status\n" +
                    "FROM good_detail gd,img_path i, order_detail od,orders o,\n" +
                    "goods_four_type four,goods_third_type three\n" +
                    " WHERE i.`gd_id`=gd.`gd_id`\n" +
                    "AND od.gd_id=gd.gd_id\n" +
                    "AND o.o_id=od.o_id\n" +
                    "AND gd.gfour_id = four.gfour_id\n" +
                    "AND gd.gtt_id = three.gtt_id\n" +
                    "AND gd.`gd_id`IN(SELECT od.`gd_id` FROM order_detail od LEFT JOIN orders o ON od.`o_id`=o.`o_id` AND o.`u_id`=? GROUP BY od.`gd_id`)\n" +
                    " GROUP BY od.od_id) res\n" +
                    "WHERE res.o_id=?");
            pstmt = (PreparedStatement) con.prepareStatement(String.valueOf(sql));
            pstmt.setObject(1,u_id);
            pstmt.setObject(2,o_id);
            rs = pstmt.executeQuery();
            while (rs.next()){
                int oid = rs.getInt("o_id");
                int od_id = rs.getInt("od_id");
                int gd_id = rs.getInt("gd_id");
                String img_path = rs.getString("img_path");
                String gtt_name = rs.getString("gtt_name");
                int gd_price = rs.getInt("gd_price");
                int gd_buyNum = rs.getInt("gd_buyNum");
                String gfour_name = rs.getString("gfour_name");
                int od_status = rs.getInt("od_status");
//                String o_pay = rs.getString("o_pay");
                String sumMoney = String.valueOf(gd_price*gd_buyNum);
                UserOrderList userOrderList = new UserOrderList(oid,od_id,gd_id,img_path,gtt_name,gfour_name,gd_price,gd_buyNum,od_status,sumMoney,null);
                list.add(userOrderList);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return list;
    }

    @Override
    public List<UserOrderList> getOrderDetailByPay(int u_id, int o_pay) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        List<UserOrderList> list = new ArrayList<>();
        try {
            con = TransactionManager.getTransactionConn();
            StringBuffer sql = new StringBuffer("SELECT*FROM(SELECT o.o_id,od.od_id,gd.`gd_id`,i.`img_path`,three.`gtt_name`,gd.`gd_price`,gd.`gd_buyNum`,four.`gfour_name`,od.od_status\n" +
                    " FROM good_detail gd,img_path i, order_detail od,orders o,\n" +
                    " goods_four_type four,goods_third_type three,goods_second_type secon,goods_first_type firs\n" +
                    " WHERE i.`gd_id`=gd.`gd_id`\n" +
                    " AND od.gd_id=gd.gd_id\n" +
                    " AND o.o_id=od.o_id\n" +
                    " AND o.o_pay=?\n" +
                    " AND gd.`gd_id`IN(SELECT od.`gd_id` FROM order_detail od LEFT JOIN orders o ON od.`o_id`=o.`o_id` AND o.`u_id`=? GROUP BY od.`gd_id`)\n" +
                    " GROUP BY od.od_id) res");
            pstmt = (PreparedStatement) con.prepareStatement(String.valueOf(sql));
            pstmt.setObject(1,o_pay);
            pstmt.setObject(2,u_id);
            rs = pstmt.executeQuery();
            while (rs.next()){
                int oid = rs.getInt("o_id");
                int od_id = rs.getInt("od_id");
                int gd_id = rs.getInt("gd_id");
                String img_path = rs.getString("img_path");
                String gtt_name = rs.getString("gtt_name");
                int gd_price = rs.getInt("gd_price");
                int gd_buyNum = rs.getInt("gd_buyNum");
                String gfour_name = rs.getString("gfour_name");
                int od_status = rs.getInt("od_status");
                String sumMoney = String.valueOf(gd_price*gd_buyNum);
                UserOrderList userOrderList = new UserOrderList(oid,od_id,gd_id,img_path,gtt_name,gfour_name,gd_price,gd_buyNum,od_status,sumMoney,null);
                list.add(userOrderList);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return list;
    }

    @Override
    public Order getUserOrderByUidAndOid(int u_id, int o_id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs =null;
        try {
            con = TransactionManager.getTransactionConn();
            StringBuffer sql = new StringBuffer("SELECT o.`o_id`,o.`u_id`,o.`o_status`,o.`o_pay`,o.`o_total`,o.`o_starttime` " +
                    "FROM orders o WHERE u_id=? AND o.`o_id`=?;");
            pstmt = (PreparedStatement) con.prepareStatement(String.valueOf(sql));
            pstmt.setObject(1,u_id);
            pstmt.setObject(2,o_id);
            rs = pstmt.executeQuery();
            if (rs.next()){
                int oid =rs.getInt("o_id");
                int uid = rs.getInt("u_id");
                int o_status = rs.getInt("o_status");
                int o_pay = rs.getInt("o_pay");
                int o_total = rs.getInt("o_total");
                String o_starttime = rs.getString("o_starttime");
                Order order = new Order(oid,uid,o_total,o_pay,o_status,o_starttime);
                return order;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return null;
    }

    @Override
    public void delectOrder(int uid, int oid) throws DAOException, SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        conn = TransactionManager.getTransactionConn();
        try {
            pstmt = conn.prepareStatement("DELETE FROM orders  WHERE u_id =? AND o_id=?;");
            pstmt.setInt(1,uid);
            pstmt.setInt(2,oid);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DAOException(e);
        }finally{
            ConnectionUtils.close(rs, pstmt, null);
        }
    }


    public static void main(String[] args) throws DAOException, SQLException {
        UserOrderDaoImpl userOrder = new UserOrderDaoImpl();
        List<Order> list = new ArrayList<>();
//        list = userOrder.getAllUserOrder(1,0,3);
//        System.out.println(list);
//        userOrder.userUpOrder(1,1,11);
//        System.out.println(userOrder.getAllOrderDetialList(1,null,0,3));
//        List<UserOrderList> userOrderLists = userOrder.getAllOrderDetialList(1,null,0,3);
//        System.out.println(userOrderLists);
//        System.out.println(userOrder.getAllUserOrderLike(1,"手机",0,3));
//        System.out.println(userOrder.getOrderDetailByOid(1,1));
//        System.out.println(userOrder.getUserOrderByUidAndOid(1,1));
//        userOrder.delectOrder(1,4);
        System.out.println(userOrder.getAllUserOrderLike(3,"水",0,5));
    }

}
